package com.ubst.smartad.impl;

import java.sql.*;
import java.util.*;
import java.io.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.ubst.smartad.dao.TSASysGroupDO;
import com.ubst.smartad.common.TimeUtil;
import com.ubst.smartad.logger.Debug;

public class TSASysGroupMngImpl {
	
	Connection con = null;
	private String serverName;
	private Debug debug;

	/** 
     * 생성자
     *   
     *   
    */ 
	public TSASysGroupMngImpl(Connection con, String serverName, String log_level) {
		this.con = con;
		this.serverName = serverName;
		this.debug = new Debug("TSASysGroupMngImpl", log_level);

	}
	
    /** 
	 * @author KJH
     * 시스템 그룹등록
     *   
     */  
    public void insertSysGroup(TSASysGroupDO sysGroupDO) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {

			long l_cur_date = TimeUtil.getCurrentTime();

            queryBuf = new StringBuffer();
			queryBuf.append("INSERT INTO TSA_SYSGROUP " )
				.append(" ( ")
				.append("  GROUPID ,PGROUPID ,GROUPNAME ,SCOMMENT ,CREATEDATE ")
				.append(" ) ")
				.append(" VALUES")
				.append(" ( ") 
				.append("  ? ,? ,? ,? ,? ")
				.append(" ) ");

			debug.debug("insertSysGroup ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, sysGroupDO.groupid);
			pstmt.setString(idx++, sysGroupDO.pgroupid);
			pstmt.setString(idx++, sysGroupDO.groupname);
			pstmt.setString(idx++, sysGroupDO.scomment);
			pstmt.setString(idx++, TimeUtil.getDateString(l_cur_date, "yyyyMMddHHmmss"));

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }

    /** 
	 * @author KJH
     * 시스템 그룹정보 변경
     *   
     */  
    public void updateSysGroup(TSASysGroupDO sysGroupDO) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {

			long l_cur_date = TimeUtil.getCurrentTime();

            queryBuf = new StringBuffer();
			queryBuf.append("UPDATE TSA_SYSGROUP " )
				.append(" SET ")
				.append(" PGROUPID=? ,GROUPNAME=? ,SCOMMENT=? ")
				.append(" WHERE GROUPID=? ");

			debug.debug("updateSysGroup ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, sysGroupDO.pgroupid);
			pstmt.setString(idx++, sysGroupDO.groupname);
			pstmt.setString(idx++, sysGroupDO.scomment);
			pstmt.setString(idx++, sysGroupDO.groupid);

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }

	/**
	 * @author : KJH 
	 * 시스템 그룹정보 출력
	 *
	 */
	public TSASysGroupDO selectSysGroup(String groupid) throws SQLException {

		PreparedStatement pstmt=null;
		ResultSet rs = null;

		TSASysGroupDO sysGroupDO = null;

		try {
			
			StringBuffer queryBuf = new StringBuffer();
			queryBuf.append("SELECT ")
				.append("  PGROUPID ,GROUPNAME ,SCOMMENT ,CREATEDATE ")
				.append(" FROM TSA_SYSGROUP ")
				.append(" WHERE GROUPID=? ");

			debug.debug("selectSysGroup ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, groupid);

			rs = pstmt.executeQuery();

			if ( rs.next() ){
				sysGroupDO = new TSASysGroupDO();
				sysGroupDO.groupid = groupid;
				sysGroupDO.pgroupid = rs.getString("PGROUPID");
				sysGroupDO.groupname = rs.getString("GROUPNAME");
				sysGroupDO.scomment = rs.getString("SCOMMENT");
				sysGroupDO.createdate = rs.getString("CREATEDATE");
			}

			rs.close();
			pstmt.close();

		}catch (SQLException e){
			throw e;
		}finally{
			if ( rs != null) try{rs.close();}catch(SQLException ee){}
			if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
		}

		return sysGroupDO;
	}

	/**
	 * @author : KJH 
	 * 시스템 그룹정보 리스트 출력
	 *
	 */
	public Vector selectSysGroupList() throws SQLException {

		PreparedStatement pstmt=null;
		ResultSet rs = null;

		Vector sysGroupDOList = new Vector();
		TSASysGroupDO sysGroupDO = null;

		try {
			
			StringBuffer queryBuf = new StringBuffer();
			queryBuf.append("SELECT ")
				.append("  GROUPID ,PGROUPID ,GROUPNAME ,SCOMMENT ,CREATEDATE ")
				.append(" FROM TSA_SYSGROUP ");

			debug.debug("selectSysGroupList ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );

			rs = pstmt.executeQuery();

			while ( rs.next() ){
				sysGroupDO = new TSASysGroupDO();
				sysGroupDO.groupid = rs.getString("GROUPID");
				sysGroupDO.pgroupid = rs.getString("PGROUPID");
				sysGroupDO.groupname = rs.getString("GROUPNAME");
				sysGroupDO.scomment = rs.getString("SCOMMENT");
				sysGroupDO.createdate = rs.getString("CREATEDATE");

				sysGroupDOList.add(sysGroupDO);
				sysGroupDO = null;
			}

			rs.close();
			pstmt.close();

		}catch (SQLException e){
			throw e;
		}finally{
			if ( rs != null) try{rs.close();}catch(SQLException ee){}
			if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
		}

		return sysGroupDOList;
	}

	/**
	 * @author : KJH 
	 * 시스템 그룹정보 리스트 출력(그룹관리자)
	 *
	 */
	public Vector selectSysGroupListOfUser(String userid) throws SQLException {

		PreparedStatement pstmt=null;
		ResultSet rs = null;

		Vector sysGroupDOList = new Vector();
		TSASysGroupDO sysGroupDO = null;

		try {
			
			StringBuffer queryBuf = new StringBuffer();
			queryBuf.append("SELECT ")
				.append("  A.GROUPID AS GROUPID ,B.PGROUPID AS PGROUPID ,B.GROUPNAME AS GROUPNAME ,B.SCOMMENT AS SCOMMENT ,B.CREATEDATE AS CREATEDATE ")
				.append(" FROM TSA_ADMIN_SYSGROUP A, TSA_SYSGROUP B ")
				.append(" WHERE A.USERID=? ")
				.append(" AND A.GROUPID=B.GROUPID ");

			debug.debug("selectSysGroupListOfUser ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, userid);

			rs = pstmt.executeQuery();
			

			while ( rs.next() ){
				sysGroupDO = new TSASysGroupDO();
				sysGroupDO.groupid = rs.getString("GROUPID");;
				sysGroupDO.pgroupid = rs.getString("PGROUPID");
				sysGroupDO.groupname = rs.getString("GROUPNAME");
				sysGroupDO.scomment = rs.getString("SCOMMENT");
				sysGroupDO.createdate = rs.getString("CREATEDATE");

				sysGroupDOList.add(sysGroupDO);
				sysGroupDO = null;
			}

			rs.close();
			pstmt.close();

		}catch (SQLException e){
			throw e;
		}finally{
			if ( rs != null) try{rs.close();}catch(SQLException ee){}
			if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
		}

		return sysGroupDOList;
	}

	/**
	 * @author : KJH 
	 * 그룹명 중복검사.
	 *
	 */
	public boolean checkDupGroupName(String groupname) throws SQLException {

		PreparedStatement pstmt=null;
		ResultSet rs = null;

		boolean ret_bool = false;

		try {

			StringBuffer qrybuf = new StringBuffer();
			qrybuf.append("SELECT ")
				.append("  GROUPID ")
				.append(" FROM TSA_SYSGROUP ")
				.append(" WHERE GROUPNAME=? ");

			pstmt = con.prepareStatement( qrybuf.toString() );
			pstmt.setString(1, groupname);

			rs = pstmt.executeQuery();

			if ( rs.next() ){
				ret_bool = true;
			}

			rs.close();
			pstmt.close();

		}catch (SQLException e){
			throw e;
		}finally{
			if ( rs != null) try{rs.close();}catch(SQLException ee){}
			if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
		}

		return ret_bool;
	}

    /** 
	 * @author KJH
     * 시스템 그룹삭제
     *   
     */  
    public void deleteSysGroup(String groupid) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {

            queryBuf = new StringBuffer();
			queryBuf.append("DELETE FROM TSA_SYSGROUP " )
				.append(" WHERE GROUPID=? ");

			debug.debug("deleteSysGroup ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, groupid);

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }

}
